package com.fc.redis.controller;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.fc.redis.entity.TbUser;
import com.fc.redis.service.UserService;
import com.fc.redis.util.DefaultExcelBean;
import com.fc.redis.util.ExcelUtil;
import com.fc.redis.util.ExportExcelBean;

/**
 * @program: fc-redis->ExcelController
 * @description: execel controller
 * @author: G_Y
 * @create: 2019-09-06 12:14
 **/
@Controller
@RequestMapping("/excel/service")
public class ExcelController {

    @Autowired
    private UserService userService;


    /**
     * 下载导入模板
     * 测试通过
     *
     * @param response
     * @param
     */
    @ResponseBody
    @RequestMapping("/downloadTemplate")
    public void downloadTemplate(@NotNull HttpServletResponse response, HttpSession session) {
        Workbook wb = null;
        try {
            ClassPathResource resource = new ClassPathResource("data1.xlsx");
            InputStream inputStream = resource.getInputStream();
            // 根据不同excel创建不同对象,Excel2003版本-->HSSFWorkbook,Excel2007版本-->XSSFWorkbook
            wb = WorkbookFactory.create(inputStream);
            response.reset();
            response.setContentType("multipart/form-data");
            if (wb.getClass().getSimpleName() == "HSSFWorkbook") {
                response.setHeader("Content-Disposition",
                        "attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xls");
            } else {
                response.setHeader("Content-Disposition",
                        "attachment; filename=" + new String("excel模板".getBytes(), "iso8859-1") + ".xlsx");
            }
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 测试通过
     *
     * @param request
     * @param response
     * @throws Exception 导出excel文件
     */
    @GetMapping("/exportexcel")
    public void exportPermMatrix(HttpServletRequest request, HttpServletResponse response) throws Exception {

        // 构造测试数据
        TbUser user = new TbUser("gaoyue", "188", "gy", new Date(), new Date());
        TbUser user2 = new TbUser("tlx", "188", "tlx", new Date(), new Date());
        List<TbUser> users = Arrays.asList(user, user2);

        // 行数据每一列对应对象的字段名称
        List<String> fieldNames = Arrays.asList("id", "name", "phone");
        ExportExcelBean<TbUser> exportExcelBean = new DefaultExcelBean<TbUser>("test1", "sheet1", new String[]{"用户id", "用户姓名", "电话"}, users, fieldNames);
        ExcelUtil.exportExcel(response, exportExcelBean);
    }

    /**
     * excel导入数据
     * 测试通过
     */
    @PostMapping(value = "/importexcel")
    @ResponseBody
//    @Transactional(rollbackFor = Exception.class)
    public Object importWatchExcel(@NotNull @RequestParam("file") MultipartFile file) {
        Map<String, Object> result = new HashMap<>();
        // contentType
        // String contentType = file.getContentType();
        // excel文件名
        // String fileName = file.getOriginalFilename();
        if (file.isEmpty()) {
            result.put("code", 500);
            result.put("message", "导入文件为空！");
            return result;
        }
        // 根据不同excel创建不同对象,Excel2003版本-->HSSFWorkbook,Excel2007版本-->XSSFWorkbook
        List<TbUser> users = ExcelUtil.excel2BeanList(file, TbUser.class, Arrays.asList("id", "name", "phone"), 2);
        users.forEach(System.out::println);
        result.put("code", 200);
        result.put("message", "导入成功!");
        return result;
    }

    /**
     * 测试通过
     *
     * @param response
     * @return
     * @throws Exception 生成user表excel ——手动写表格
     */
    @GetMapping(value = "/getUser")
    public String getUser(HttpServletResponse response) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("统计表");
        createTitle(workbook, sheet);
        List<TbUser> rows = userService.getAll();

        //设置日期格式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        for (TbUser user : rows) {
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getUserName());
            HSSFCell cell = row.createCell(3);
            cell.setCellValue(user.getCreateTime());
            cell.setCellStyle(style);
            rowNum++;
        }

        String fileName = "导出excel例子.xls";

        //生成excel文件
        this.buildExcelFile(fileName, workbook);

        //浏览器下载excel
        this.buildExcelDocument(fileName, workbook, response);

        return "download excel";
    }

    //创建表头
    private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);

        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
//        HorizontalAlignment align = HorizontalAlignment.CENTER;
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("ID");
        cell.setCellStyle(style);


        cell = row.createCell(1);
        cell.setCellValue("显示名");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("用户名");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("创建时间");
        cell.setCellStyle(style);
    }

    //生成excel文件到服务器机器
    protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    //浏览器下载excel 写出到 response
    protected void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

}
